﻿using PersistentObject.Common.Criteria;
using PersistentObject.Common.Exceptions;
using PersistentObject.Common.Schema;
using PersistentObject.Common.Sorts;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;


namespace PersistentObject.Common
{
    public abstract class BaseDialect
    {
        public SchemaHelper SchemaHelper { get; internal set; }
        public BaseDialect(SchemaConfig config)
        {
            SchemaHelper = new Schema.SchemaHelper(config);
            InitializeNamingPattern();
            InitializeDataPattern();
        }
        public BaseDialect()
        {
            SchemaHelper = new Schema.SchemaHelper();
            InitializeNamingPattern();
            InitializeDataPattern();
        }

        public BaseDialectNamingPattern NamingPattern { get; protected set; }

        public BaseDialectDataPattern DataPattern { get; protected set; }

        protected virtual void InitializeNamingPattern()
        {
            NamingPattern = new BaseDialectNamingPattern();
        }

        protected virtual void InitializeDataPattern()
        {
            DataPattern = new BaseDialectDataPattern();
        }

        public virtual BaseCriteriaCreator GetCriteriaCreator(Table mainTable)
        {
            return new BaseCriteriaCreator(mainTable, this);
        }

        public BaseSqlCreator GetSqlCreator<T>()
        {
            return GetSqlCreator(typeof(T));
        }

        public BaseSqlCreator GetSqlCreator(Type type)
        {
            Table table = SchemaHelper.CreateTable(type);
            return GetSqlCreator(table);
        }

        public virtual BaseSqlCreator GetSqlCreator(Table mainTable)
        {
            BaseSqlCreator creator = new BaseSqlCreator(mainTable, this);
            return creator;
        }

        #region ado.net object functions
        /// <summary>
        /// Create the database connection instance
        /// </summary>
        /// <param name="connectionString">Indicate the connection string</param>
        /// <returns>Return the database connection instace</returns>
        public abstract IDbConnection CreateConnection(string connectionString);

        /// <summary>
        /// Create the database transaction instance
        /// </summary>
        /// <param name="connection">Indicate the database connection instance</param>
        /// <returns>Return the transaction instance</returns>
        public abstract IDbTransaction CreateTransaction(IDbConnection connection);

        /// <summary>
        /// Create the database command instance
        /// </summary>
        /// <param name="transaction">Indicate the database transaction instance</param>
        /// <param name="connection">Indicate the database connection instance</param>
        /// <param name="sql">Indicate the sql script</param>
        /// <returns>Return the database command instance</returns>
        public abstract IDbCommand CreateCommand(IDbTransaction transaction, string sql);

        public abstract IDbCommand CreateCommand(IDbConnection connection, string sql);

        /// <summary>
        /// Create the database adapter instance
        /// </summary>
        /// <param name="connection">Indicate the database connection instance</param>
        /// <param name="sql">Indicate the sql script</param>
        /// <returns>Return the database</returns>
        public abstract IDbDataAdapter CreateAdapter(IDbConnection connection, string sql);

        /// <summary>
        /// Create the parameter instance
        /// </summary>
        /// <returns>Return the parameter instance</returns>
        public abstract IDbDataParameter CreateParameter();
        #endregion

        public virtual string CreateLoadSql<T>(BaseCriteria criteria, OrderBy[] orderBys, int pageSize, int pageIndex)
        {
            BaseSqlCreator creator = GetSqlCreator<T>();
            var sql = creator.CreateSelectSql(criteria, orderBys, pageSize, pageIndex);
            return sql;
        }

        public virtual string CreateLoadSql<T>(BaseCriteria criteria, OrderBy[] orderBys)
        {
            BaseSqlCreator creator = GetSqlCreator<T>();
            var sql = creator.CreateSelectSql(criteria, orderBys);
            return sql;
        }

        public virtual string CreateCountSql<T>(BaseCriteria criteria)
        {
            BaseSqlCreator creator = GetSqlCreator<T>();
            var sql = creator.CreateCountSql(criteria);
            return sql;
        }

        //public virtual string CreateSaveSql<T>()
        //{
        //    BaseSqlCreator creator = GetSqlCreator<T>();
        //    var sql = creator.CreateInsertSql();
        //    return sql;
        //}

        public virtual string GetInsertSql<T>()
        {
            BaseSqlCreator creator = GetSqlCreator<T>();
            var sql = creator.CreateInsertSql();
            return sql;
        }

        public virtual string GetUpdateSql<T>()
        {
            BaseSqlCreator creator = GetSqlCreator<T>();
            var sql = creator.CreateUpdateSql();
            return sql;
        }

        public virtual string GetIsExistingSql<T>()
        {
            BaseSqlCreator creator = GetSqlCreator<T>();
            var sql = creator.CreateIsExistingSql();
            return sql;
        }

        public virtual string GetDeleteSql<T>(BaseCriteria criteria)
        {
            BaseSqlCreator creator = GetSqlCreator<T>();
            var sql = creator.CreateDeleteSql(criteria);
            return sql;
        }

        public virtual string GetDeleteSql<T>()
        {
            BaseSqlCreator creator = GetSqlCreator<T>();
            var sql = creator.CreateDeleteSql();
            return sql;
        }

        public virtual IEnumerable<OperandValue> GetOperandValues(BaseCriteria criteria)
        {
            List<OperandValue> list = new List<OperandValue>();
            Queue<BaseCriteria> criteriaQueue = new Queue<BaseCriteria>();
            criteriaQueue.Enqueue(criteria);
            while (criteriaQueue.Count() > 0)
            {
                BaseCriteria current = criteriaQueue.Dequeue();
                if (current is BinaryCriteria)
                {
                    BinaryCriteria bc = current as BinaryCriteria;
                    if (bc.Left is OperandValue)
                    {
                        list.Add(bc.Left as OperandValue);
                    }
                    if (bc.Left is OperandDecorator && (bc.Left as OperandDecorator).InnerOperandValue != null)
                    {
                        list.Add((bc.Left as OperandDecorator).InnerOperandValue);
                    }
                    if (bc.Right is OperandValue)
                    {
                        list.Add(bc.Right as OperandValue);
                    }
                    if (bc.Right is OperandDecorator && (bc.Right as OperandDecorator).InnerOperandValue != null)
                    {
                        list.Add((bc.Right as OperandDecorator).InnerOperandValue);
                    }
                }
                else if (current is BetweenCriteria)
                {
                    BetweenCriteria bc = current as BetweenCriteria;
                    if (bc.A is OperandValue)
                    {
                        list.Add(bc.A as OperandValue);
                    }
                    if (bc.A is OperandDecorator && (bc.A as OperandDecorator).InnerOperandValue != null)
                    {
                        list.Add((bc.A as OperandDecorator).InnerOperandValue);
                    }
                    if (bc.B is OperandValue)
                    {
                        list.Add(bc.B as OperandValue);
                    }
                    if (bc.B is OperandDecorator && (bc.B as OperandDecorator).InnerOperandValue != null)
                    {
                        list.Add((bc.B as OperandDecorator).InnerOperandValue);
                    }
                    if (bc.C is OperandValue)
                    {
                        list.Add(bc.C as OperandValue);
                    }
                    if (bc.C is OperandDecorator && (bc.C as OperandDecorator).InnerOperandValue != null)
                    {
                        list.Add((bc.C as OperandDecorator).InnerOperandValue);
                    }
                }
                else if (current is InCriteria)
                {
                    InCriteria ic = current as InCriteria;
                    foreach (var op in ic.Values)
                    {
                        if (op is OperandValue)
                        {
                            list.Add(op as OperandValue);
                        }
                        if (op is OperandDecorator && (op as OperandDecorator).InnerOperandValue != null)
                        {
                            list.Add((op as OperandDecorator).InnerOperandValue);
                        }
                    }
                }
                else if (current is AndCriteria)
                {
                    AndCriteria ac = current as AndCriteria;
                    foreach (var c in ac.CriteriaList)
                    {
                        criteriaQueue.Enqueue(c);
                    }
                }
                else if (current is OrCriteria)
                {
                    OrCriteria oc = current as OrCriteria;
                    foreach (var c in oc.CriteriaList)
                    {
                        criteriaQueue.Enqueue(c);
                    }
                }
                else if (current is NotCriteria)
                {
                    NotCriteria nc = current as NotCriteria;
                    criteriaQueue.Enqueue(nc.HostedCriteria);
                }
                else if(current is ExistsCriteria)
                {
                    ExistsCriteria ec = current as ExistsCriteria;
                    if(ec.InnerCriteria != null)
                    {
                        criteriaQueue.Enqueue(ec.InnerCriteria);
                    }
                }
            }
            return list;
        }

        public virtual IEnumerable<IDataParameter> PrepareCriteriaParameters<T>(BaseCriteria criteria)
        {
            List<IDataParameter> list = new List<IDataParameter>();
            if (criteria == null) return list;

            Table mainTable = SchemaHelper.CreateTable<T>();
            BaseCriteriaCreator CriteriaCreator = GetCriteriaCreator(mainTable);

            var operandValues = GetOperandValues(criteria);
            foreach (var operandValue in operandValues)
            {
                var parameter = CreateParameter();
                parameter.ParameterName = NamingPattern.GetOperandValueParameterName(operandValue);
                parameter.Value = operandValue.Value;
                parameter.DbType = DataPattern.GetDbType(operandValue.Value.GetType());
                parameter.Value = DataPattern.Validate(parameter.Value);
                list.Add(parameter);
            }
            return list;
        }

        public virtual void PrepareExistingParameters<T>(IDbCommand command, T obj)
        {
            Table table = SchemaHelper.CreateTable<T>();
            string sql = command.CommandText;

            var keyParam = CreateSaveParameter(table.KeyColumn, obj);
            if (sql.IndexOf(keyParam.ParameterName) >= 0)
            {
                command.Parameters.Add(keyParam);
            }
        }

        public virtual void PrepareSaveParameters<T>(IDbCommand command, T obj)
        {
            Table table = SchemaHelper.CreateTable<T>();
            string sql = command.CommandText;

            var keyParam = CreateSaveParameter(table.KeyColumn, obj);
            keyParam.Direction = ParameterDirection.InputOutput;
            if (sql.IndexOf(keyParam.ParameterName) >= 0)
            {
                command.Parameters.Add(keyParam);
            }

            foreach (var column in table.ColumnList)
            {
                var colParam = CreateSaveParameter(column, obj);
                if (sql.IndexOf(colParam.ParameterName) >= 0)
                {
                    command.Parameters.Add(colParam);
                }
            }
        }

        public virtual void PrepareDeleteParameters<T>(IDbCommand command, T obj)
        {
            /* create parameter for key column
             * create parameter for each column
             * */
            Table table = SchemaHelper.CreateTable<T>();
            string sql = command.CommandText;
            var pname = NamingPattern.GetDeleteParameterName(table.KeyColumn);
            if (sql.IndexOf(pname) >= 0)
            {
                var keyParam = CreateDeleteParameter(table.KeyColumn, obj);
                command.Parameters.Add(keyParam);
            }

            foreach (var column in table.ColumnList)
            {
                pname = NamingPattern.GetDeleteParameterName(column);
                if (sql.IndexOf(pname) >= 0)
                {
                    var colParam = CreateDeleteParameter(column, obj);
                    command.Parameters.Add(colParam);
                }
            }
        }

        public virtual IDataParameter CreateSaveParameter(Column column, object obj)
        {
            /* create a parameter object by the dialect
             * set the parameter name
             * if column is a foreign column then set foreign id value and db type
             * else if column has a convertor then set persisted value and db type
             * else set object value and dialectical db type
             * */
            IDataParameter parameter = CreateParameter();
            parameter.ParameterName = NamingPattern.GetSaveParameterName(column);
            if (column.IsForeignKeyColumn)
            {
                object value = column.Property.GetValue(obj);
                parameter.Value = value == null ? null : column.ForeignTable.KeyColumn.Property.GetValue(value);
                parameter.DbType = DataPattern.GetDbType(column.ForeignTable.KeyColumn.Property.PropertyType);
            }
            else if (column.Convertor != null)
            {
                object value = column.Property.GetValue(obj);
                parameter.Value = column.Convertor.PersistFrom(value);
                parameter.DbType = column.Convertor.GetDbType();
            }
            else
            {
                parameter.Value = column.Property.GetValue(obj);
                parameter.DbType = DataPattern.GetDbType(column.Property.PropertyType);
            }
            parameter.Value = DataPattern.Validate(parameter.Value);
            return parameter;
        }

        public virtual IDataParameter CreateDeleteParameter(Column column, object obj)
        {
            /* create a parameter object by the dialect
             * set the parameter name
             * if column is a foreign column then set foreign id value and db type
             * else if column has a convertor then set persisted value and db type
             * else set object value and dialectical db type
             * */

            IDataParameter parameter = CreateParameter();
            parameter.ParameterName = NamingPattern.GetDeleteParameterName(column);
            if (column.IsForeignKeyColumn)
            {
                var value = column.Property.GetValue(obj);
                parameter.Value = value == null ? null : column.ForeignTable.KeyColumn.Property.GetValue(value);
                parameter.DbType = DataPattern.GetDbType(column.ForeignTable.KeyColumn.Property.PropertyType);
            }
            else if (column.Convertor != null)
            {
                var value = column.Property.GetValue(obj);
                parameter.Value = column.Convertor.PersistFrom(value);
                parameter.DbType = column.Convertor.GetDbType();
            }
            else
            {
                parameter.Value = column.Property.GetValue(obj);
                parameter.DbType = DataPattern.GetDbType(column.Property.PropertyType);
            }
            parameter.Value = DataPattern.Validate(parameter.Value);
            return parameter;
        }

        public abstract object RetrieveKeyValue<T>(IDataReader reader, IDbCommand command);
    }
}
